﻿/*创建人：盛金龙
 *时间：2011/12/30 周五
 *用途：建设单位管理
 */
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using EnvironmentManagerSystem.MODE;
using MySql.Data.MySqlClient;

namespace EnvironmentManagerSystem.BLL
{
    public class ConstructionUnit
    {
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetConstructionUnit(string UnitName, string lianxiren, string ren)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" id");
                br.Append(",Name ");
                br.Append(",Address");
                br.Append(",Phone");
                br.Append(",ZipCode");
                br.Append(",State");
                br.Append(",ApplicationTime");
                br.Append(",ren");
                br.Append(",lianxiren");
                br.Append(" from tbs001_constructionunit ");
                br.Append(" where 1=1");
                if (UnitName != string.Empty)
                    br.AppendFormat(" and Name like '%{0}%'", UnitName);
                if (lianxiren != string.Empty)
                    br.AppendFormat(" and lianxiren like '%{0}%'", lianxiren);
                if (ren != string.Empty)
                    br.AppendFormat(" and ren like '%{0}%'", ren);
                br.Append(" order by ApplicationTime ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="p"></param>
        public static void DeleteConstructionUnit(string id)
        {
            try
            {
                DbHelperMySQL.ExecuteSql(string.Format(" delete from tbs001_constructionunit where id='{0}'", id));
            }
            catch { }
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="cud"></param>
        /// <returns></returns>
        public static bool UpdateConstructionUnit(ConstructionUnitData cud)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" update tbs001_constructionunit set");
                br.AppendFormat("  Name = '{0}' ", cud.Name);
                br.AppendFormat(" ,Address = '{0}' ", cud.Address);
                br.AppendFormat(" ,Phone = '{0}' ", cud.Phone);
                br.AppendFormat(" ,ZipCode = '{0}' ", cud.ZipCode);
                br.AppendFormat(" ,State = '{0}' ", cud.State);
                br.AppendFormat(" ,ApplicationTime = '{0}' ", cud.ApplicationTime);
                br.AppendFormat(" ,ren = '{0}' ", cud.Ren);
                br.AppendFormat(" ,lianxiren = '{0}' ", cud.Lianxiren);
                br.AppendFormat(" where id = {0} ", cud.ID);
                return DbHelperMySQL.ExecuteSql(br.ToString()) > 0 ? true : false;
            }
            catch { return false; }
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="cud"></param>
        /// <returns></returns>
        public static bool AddConstructionUnit(ConstructionUnitData cud)
        {
            try
            {
                DataTable dt = DbHelperMySQL.Query(string.Format(" select UserName from tbs001_user where UserName = '{0}' ", cud.uid)).Tables[0];
                if (dt.Rows.Count <= 0)
                {
                    StringBuilder br = new StringBuilder();
                    br.Append(" insert into tbs001_constructionunit(Name,Address,Phone,ZipCode,chuanzhen,youxiang,ren,lianxiren)values(");
                    br.AppendFormat(" '{0}' ", cud.Name);
                    br.AppendFormat(",'{0}' ", cud.Address);
                    br.AppendFormat(",'{0}' ", cud.Phone);
                    br.AppendFormat(",'{0}' ", cud.ZipCode);
                    br.AppendFormat(",'{0}' ", cud.chuanzhen);
                    br.AppendFormat(",'{0}' ", cud.youxiang);
                    br.AppendFormat(",'{0}' ", cud.Ren);
                    br.AppendFormat(",'{0}');select max(id) from tbs001_constructionunit ", cud.Lianxiren);
                    DataTable dtd = DbHelperMySQL.Query(br.ToString()).Tables[0];
                    return DbHelperMySQL.ExecuteSql(string.Format("insert into tbs001_user (UserName,LoginName,Password,InUse,userid,RoleID) values ('{0}','{1}','{2}','1','{3}','建设单位'); ", cud.Name, cud.uid, cud.pwd, dtd.Rows[0][0].ToString())) > 0 ? true : false;
                }
                return false;
            }
            catch { return false; }
        }


        /// <summary>
        /// 查询环评单位数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetList()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" id");
                br.Append(",UnitName as EIAUnitName ");
                br.Append(" from tbs001_evaluationunit WHERE Enabled = 1");
                br.Append(" order by UnitName ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        /// <summary>
        /// 查询建设单位数据
        /// </summary>
        /// <returns></returns>
        public static DataTable GetSelectTbs001_constructionunit()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" ID");
                br.Append(",NAME as JSName ");
                br.Append(" from tbs001_constructionunit");
                br.Append(" order by NAME ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        /// <summary>
        /// 查询项目负责人
        /// </summary>
        /// <returns></returns>
        public static DataTable GetRen()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" id");
                br.Append(",Name ");
                br.Append(" from tbs001_projectleader ");
                br.Append(" order by id ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
      
        public static DataTable GetRenWhere(string where, string where1)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.AppendFormat(@"select tbs001_projectleader.id ,tbs001_projectleader.Name from tbs001_projectleader,tbs001_basicdatasub,
                                tbs001_evaluationunit where tbs001_projectleader.UnitID=tbs001_evaluationunit.ID and 
                                tbs001_projectleader.EIAType=tbs001_basicdatasub.ID and tbs001_projectleader.UnitID = '" + where + "' and tbs001_projectleader.EIAType = '" + where1 + "' order by id ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        public static DataTable Gethylb(string id)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" id");
                br.Append(",DataValue as Name ");
                br.AppendFormat(" from tbs001_basicdatasub where MainID = '{0}' ", id);
                br.Append(" order by id ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <returns></returns>
        public static DataTable Gethylb()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" id");
                br.Append(",DataValue as Name ");
                br.Append(" from tbs001_basicdatasub where MainID = '7' and (DataValue like '%输变电%' or DataValue like '%核工业%')");
                br.Append(" order by id ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        /// <summary>
        /// 查询项目地区代码
        /// </summary>
        /// <returns></returns>
        public static DataTable Gethylb(string id, string where)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" DataValueMemo as id");
                br.Append(",DataValue as Name ");
                br.AppendFormat(" from tbs001_basicdatasub where MainID = '{0}' and DataValueMemo LIKE '{1}' ", id, where);
                br.Append(" order by id ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }

        /// <summary>
        /// 查询修改数据数据
        /// </summary>
        /// <returns></returns>
        public static void SetRen(string id, string NAME, string str)
        {
            try
            {
                #region 修改数据
                if (str.Equals("ren"))
                {
                    StringBuilder brb = new StringBuilder();
                    brb.Append(" update tbs001_developprojectbasicinfo set ");
                    brb.AppendFormat(" xiangmufuzheren = {0} ", id);
                    brb.AppendFormat(" where ProjectName = '{0}' ", NAME);
                    DbHelperMySQL.ExecuteSql(brb.ToString());
                }
                else if (str.Equals("1"))
                {
                    StringBuilder brb = new StringBuilder();
                    brb.Append(" update tbs001_developprojectbasicinfo set ");
                    brb.AppendFormat(" IndustryType1 = {0} ", id);
                    brb.AppendFormat(" where ProjectName = '{0}' ", NAME);
                    DbHelperMySQL.ExecuteSql(brb.ToString());
                }
                else if (str.Equals("2"))
                {
                    StringBuilder brb = new StringBuilder();
                    brb.Append(" update tbs001_developprojectbasicinfo set ");
                    brb.AppendFormat(" IndustryType2 = {0} ", id);
                    brb.AppendFormat(" where ProjectName = '{0}' ", NAME);
                    DbHelperMySQL.ExecuteSql(brb.ToString());
                }
                else if (str.Equals("3"))
                {
                    StringBuilder brb = new StringBuilder();
                    brb.Append(" update tbs001_developprojectbasicinfo set ");
                    brb.AppendFormat(" IndustryType3 = {0} ", id);
                    brb.AppendFormat(" where ProjectName = '{0}' ", NAME);
                    DbHelperMySQL.ExecuteSql(brb.ToString());
                }
                else if (str.Equals("4"))
                {
                    StringBuilder brb = new StringBuilder();
                    brb.Append(" update tbs001_developprojectbasicinfo set ");
                    brb.AppendFormat(" ESIndustryType = {0} ", id);
                    brb.AppendFormat(" where ProjectName = '{0}' ", NAME);
                    DbHelperMySQL.ExecuteSql(brb.ToString());
                }
                else if (str.Equals("5"))
                {
                    StringBuilder brb = new StringBuilder();
                    brb.Append(" update tbs001_developprojectbasicinfo set ");
                    brb.AppendFormat(" huanpingleibie = {0} ", id);
                    brb.AppendFormat(" where ProjectName = '{0}' ", NAME);
                    DbHelperMySQL.ExecuteSql(brb.ToString());
                }
                #endregion
            }
            catch { }
        }

        /// <summary>
        /// 修改保存及项目状态
        /// </summary>
        public static void SetStatus(string name, string str)
        {
            try
            {
                StringBuilder brb = new StringBuilder();
                brb.Append(" update tbs001_developprojectbasicinfo set ");
                brb.AppendFormat(" projectreportstate = {0}, ", str.Equals("save") ? 0 : 1);
                brb.AppendFormat(" shoulituihuishanchu = NULL ");
                brb.AppendFormat(" where ProjectName = '{0}' ", name);
                DbHelperMySQL.ExecuteSql(brb.ToString());
            }
            catch { }
        }
        public static void bts(string BiangengTuihuichongbao, string name)
        {
            //try
            //{
            //    StringBuilder brb = new StringBuilder();
            //    brb.Append(" update tbs001_developprojectbasicinfo set ");
            //    brb.AppendFormat(" shoulituihuishanchu = '{0}' ",BiangengTuihuichongbao);
            //    brb.AppendFormat(" where ProjectName = '{0}' ", name);
            //    DbHelperMySQL.ExecuteSql(brb.ToString());
            //}
            //catch { }
        }
        #region 验证建设单位是否存在
        public static UserDataCollection GetByUserName(string UserName)
        {
            UserDataCollection hs = new UserDataCollection();
            try
            {
                StringBuilder br = new StringBuilder();
                br.AppendFormat(@"select UserName,LoginName from tbs001_user");
                br.AppendFormat(" WHERE UserName ='" + UserName + "'");
                MySqlDataReader dr = DbHelperMySQL.ExecuteReader(br.ToString());
                while (dr.Read())
                {
                    UserData model = new UserData();
                    if (dr[0] != DBNull.Value)
                        model.UserName = dr[0].ToString();
                    if (dr[1] != DBNull.Value)
                        model.LoginName = dr[1].ToString();
                    hs.Add(model);
                }
                return hs;
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region 根据环评单位信息查出负责人(x)
        public static DataTable GetRens(string UnitID)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.AppendFormat(@"select id,Name from tbs001_projectleader where UnitID='"+UnitID+"' order by id ");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion
    }
}
